package com.woniuxy.microsportsserver.sleepmag.mapper;

import com.woniuxy.microsportsserver.bean.SleepAnalysisInfo;
import com.woniuxy.microsportsserver.bean.SleepInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SleepMapper {

    List<SleepInfo> findAll(@Param("s") SleepInfo sleepInfo);

    //近一年深睡浅睡的平均数
    @Select("SELECT SUM(deep_value)/COUNT(*) AS deepValueAvg," +
            "SUM(shallow_value)/COUNT(*) AS shallowValueAvg," +
            "MONTH(TIME) AS timeType FROM sleep_info " +
            "WHERE TIME BETWEEN DATE_SUB(NOW(), INTERVAL 365 DAY)" +
            " AND DATE_SUB(NOW(), INTERVAL 1 DAY) GROUP BY MONTH(TIME)")
    List<SleepAnalysisInfo> findAvgSleepValue();


    //睡眠时间占比
 /*   @Select(" SELECT (CASE" +
            "WHEN a.sleepAvg IS NULL THEN '未知'" +
            "WHEN a.sleepAvg BETWEEN 0 AND 3 THEN '三个小时以下'" +
            "WHEN a.sleepAvg BETWEEN 3 AND 4 THEN '3~4个小时'" +
            "WHEN a.sleepAvg BETWEEN 4 AND 5 THEN '4~5个小时'" +
            "WHEN a.sleepAvg >= 5 THEN '5个小时以上'" +
            "END) AS timeType," +
            "COUNT(*)AS sleepValueAvg  FROM" +
            "(SELECT (SUM(deep_value)+SUM(shallow_value))/COUNT(id) AS sleepAvg , user_id" +
            " FROM sleep_info WHERE TIME BETWEEN DATE_SUB(NOW(), INTERVAL 365 DAY) AND DATE_SUB(NOW(), INTERVAL 1 DAY)GROUP BY user_id) a" +
            " GROUP BY timeType")*/
    List<SleepAnalysisInfo> findSleepPercentage();


}
